package com.nbjtjc.safe.mapper.cost;

import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.nbjtjc.safe.model.cost.CostBudgetMonth;
import com.nbjtjc.util.MyMapper;

public interface CostBudgetMonthMapper extends MyMapper<CostBudgetMonth> {
	
	@Select("select * from cost_budget_month where  cancel = 0 and item_type = 1 and item_id = #{itemId}")
	CostBudgetMonth findItemByItemId(@Param("itemId") int itemId);
	
	@Select("select * from cost_budget_month where  cancel = 0 and item_type = 2 and item_id = #{itemId}")
	CostBudgetMonth findSubItemByItemId(@Param("itemId") int itemId);
	
	@Select("select * from cost_budget_month where  cancel = 0 and item_type = 3 and plan_id = #{planId}")
	CostBudgetMonth findPlanByPlanId(@Param("planId") int planId);


	
	@Update(" UPDATE cost_budget_month c  INNER JOIN "
+" (SELECT b.budget_item_id AS item_id,SUM(a.jan) AS jan,SUM(a.feb) AS feb,SUM(a.mar) AS mar,SUM(apr) AS apr,SUM(a.may) "
+" AS may,SUM(jun) AS jun,SUM(a.jul) AS jul,SUM(a.aug) AS aug,SUM(a.sep) AS sep,SUM(a.oct) AS `oct`,SUM(a.nov) AS nov,SUM(a.dece) AS dece,SUM(a.total_budget) AS total_budget,SUM(a.total_plan_budget) AS total_plan_budget  "
+" FROM cost_budget_month a  INNER JOIN cost_budget_sub_item b ON a.item_id = b.id WHERE a.cancel=0 and b.cancel =0 AND a.plan_id = #{planId} AND a.item_type = 2 GROUP BY b.budget_item_id) d "
+" ON c.item_id = d.item_id "
+" SET c.jan=d.jan,c.feb=d.feb,c.mar=d.mar,c.apr=d.apr,c.may=d.may,c.jun=d.jun,c.jul=d.jul,c.aug=d.aug,c.sep=d.sep,c.oct=d.oct,c.nov=d.nov,c.dece=d.dece,c.total_budget=d.total_budget,c.total_plan_budget=d.total_plan_budget "
+" WHERE c.cancel=0 AND  c.plan_id = #{planId} AND c.item_type = 1 ")
	int updateItemMonth(@Param("planId") Integer planId);//根据预算计划ID,更新所有一级项目的各月预算

//	@Update(" UPDATE cost_budget_month c  INNER JOIN "
// +"(SELECT b.parent_id AS temp_id,SUM(a.jan) AS jan,SUM(a.feb) AS feb,SUM(a.mar) AS mar,SUM(apr) AS apr,SUM(a.may) "
//+"AS may,SUM(jun) AS jun,SUM(a.jul) AS jul,SUM(a.aug) AS aug,SUM(a.sep) AS sep,SUM(a.oct) AS `oct`,SUM(a.nov) AS nov,SUM(a.dece) AS dece,SUM(a.total_budget) AS total_budget,SUM(a.total_plan_budget) AS total_plan_budget  "
//+"FROM cost_budget_month a  INNER JOIN cost_budget_plan_templet b ON a.item_temp_id = b.id WHERE a.cancel=0 and a.plan_id = #{planId} AND a.item_type = 2 GROUP BY b.parent_id) d "
//+"ON c.item_temp_id = d.temp_id "
//+"SET c.jan=d.jan,c.feb=d.feb,c.mar=d.mar,c.apr=d.apr,c.may=d.may,c.jun=d.jun,c.jul=d.jul,c.aug=d.aug,c.sep=d.sep,c.oct=d.oct,c.nov=d.nov,c.dece=d.dece,c.total_budget=d.total_budget,c.total_plan_budget=d.total_plan_budget"
//+" WHERE c.cancel=0 and  c.plan_id = #{planId} AND c.item_type = 1 ")
//	int updateItemMonthTemplet(@Param("planId") Integer planId);//根据预算计划ID,更新所有项目的各月预算
	
	@Update(" UPDATE cost_budget_month b INNER JOIN"
 +"( SELECT a.plan_id AS plan_id,SUM(a.jan) AS jan,SUM(a.feb) AS feb,SUM(a.mar) AS mar,SUM(apr) AS apr,SUM(a.may) "
+" AS may,SUM(jun) AS jun,SUM(a.jul) AS jul,SUM(a.aug) AS aug,SUM(a.sep) AS sep,SUM(a.oct) AS `oct`,SUM(a.nov) AS nov,SUM(a.dece) AS dece ,SUM(a.total_budget) AS total_budget,SUM(a.total_plan_budget) AS total_plan_budget  "
 +" FROM cost_budget_month a WHERE a.cancel = 0 AND a.item_type=1 AND a.plan_id=#{planId} GROUP BY a.plan_id ) d "
+" ON b.plan_id = d.plan_id "
+" SET b.jan=d.jan,b.feb=d.feb,b.mar=d.mar,b.apr=d.apr,b.may=d.may,b.jun=d.jun,b.jul=d.jul,b.aug=d.aug,b.sep=d.sep,b.oct=d.oct,b.nov=d.nov,b.dece=d.dece,b.total_budget=d.total_budget,b.total_plan_budget=d.total_plan_budget "
+" WHERE b.cancel = 0 AND b.item_type=3 AND b.plan_id=#{planId} ")
	int updatePlanMonth(@Param("planId") Integer planId);//根据预算计划ID,更新月度预算表中的总预算
	


//	@Select("select * from cost_budget_month where cancel = 0 and item_type=1 and plan_id=#{planId}")
//	List<CostBudgetMonth> findItemMonthByPlanId(@Param("planId") Integer planId);
	
//	@Select("")
//	int updateMonthPlanAccount(@Param("planId") int planId);
}